package com.je21121.dao;

import com.je21121.model.MyPage;
import com.je21121.model.UserInfo;
import com.je21121.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//  用户表连接管理
public class UserDao {
    //  用户登录
    public boolean userlogin(UserInfo userInfo){
        // 创建连接
        try {
            Connection connection = JdbcUtil.getConnection();
            String sql = "select * from userinfo where username=? and password=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,userInfo.getUsername());
            preparedStatement.setString(2,userInfo.getPassword());
            ResultSet resultSet = preparedStatement.executeQuery();// select
            boolean flag = resultSet.next();
            JdbcUtil.close(connection,preparedStatement,resultSet);//关闭连接
            return flag;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean addUser(UserInfo userInfo) {
        // 创建连接
        try {
            Connection connection = JdbcUtil.getConnection();
            //  插入sql 语句
            String sql = "insert into userinfo values (null,?,?,?)";
            //  赋值
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,userInfo.getUsername());
            preparedStatement.setString(2,userInfo.getPassword());
            preparedStatement.setInt(3,userInfo.getType());
            int flag = preparedStatement.executeUpdate();//  执行 sql 增删改语句
            JdbcUtil.close(connection,preparedStatement);
            if(flag > 0){//   sql执行成功
                return true;
            }else{
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public List<UserInfo> getUserlist(UserInfo userInfo) {
        List<UserInfo> list = new ArrayList<>();
        // 创建连接
        try {
            Connection connection = JdbcUtil.getConnection();
            String sql = "";
            PreparedStatement preparedStatement = null;
            MyPage page = userInfo.getPage();
            if(userInfo.getUsername() == null || userInfo.getUsername().equals("")){
                sql = "select * from userinfo limit ?,? ";// 如果参数为空  就是查询全部
                preparedStatement = connection.prepareStatement(sql);
                //  （当前页-1）* 每页显示数
                // 第一页 0  ,2 从第1行数据开始 返回2条数据   第二页  2,2 从第3行数据   第三页  4,2 从5行数据
                preparedStatement.setInt(1,(page.getCurpage()-1)*page.getPagecount());
                preparedStatement.setInt(2,page.getPagecount());// 每页返回数据
            }else{
                sql = "select * from userinfo where username like concat('%',?,'%') limit ?,?";// 参数不为空
                preparedStatement = connection.prepareStatement(sql);
                //preparedStatement.setString(1,"%"+userInfo.getUsername()+"%");
                preparedStatement.setString(1,userInfo.getUsername());
                preparedStatement.setInt(2,(page.getCurpage()-1)*page.getPagecount());
                preparedStatement.setInt(3,page.getPagecount());
            }
            System.out.println(preparedStatement);
            ResultSet resultSet = preparedStatement.executeQuery();// select
            while (resultSet.next()){
                UserInfo userInfo1 = new UserInfo();
                userInfo1.setId(resultSet.getInt("id"));// 获取用户id
                userInfo1.setUsername(resultSet.getString("username"));
                System.out.println(userInfo1.getUsername());
                userInfo1.setPassword(resultSet.getString("password"));
                userInfo1.setType(resultSet.getInt("type"));
                list.add(userInfo1);
            }
            JdbcUtil.close(connection,preparedStatement,resultSet);//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public boolean deleteUser(Integer[] uids) {
        // 创建连接
        try {
            Connection connection = JdbcUtil.getConnection();
            //  删除 sql 语句
            String sql = "delete from userinfo where id = ? ";
            //  赋值
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for(Integer id : uids){
                preparedStatement.setInt(1,id);
                preparedStatement.addBatch();//  生成批量sql
                System.out.println(preparedStatement);
            }
            //  返回每一个sql的执行结果
            int[] ints = preparedStatement.executeBatch();//  批量执行 sql 增删改语句
            JdbcUtil.close(connection,preparedStatement);
            if(ints[0] > 0){//   sql执行成功
                return true;
            }else{
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    //   只能传一个参数
    public UserInfo getuser(UserInfo userInfo) {
        //   创建返回对象
        UserInfo user = new UserInfo();
        // 创建连接
        try {
            Connection connection = JdbcUtil.getConnection();
            String sql = "select * from userinfo ";
            PreparedStatement preparedStatement = null;
            // 如果username 不为空  sql语句加上对应的where条件
            if(userInfo.getUsername() != null && !userInfo.getUsername().equals("")){
                sql += " where username = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,userInfo.getUsername());
            }else {
                // 用户id 不为空
                sql += " where id = ? ";
                preparedStatement = connection.prepareStatement(sql);
                //preparedStatement.setString(1,"%"+userInfo.getUsername()+"%");
                preparedStatement.setInt(1,userInfo.getId());
            }
            System.out.println(preparedStatement);
            ResultSet resultSet = preparedStatement.executeQuery();// select
            if(resultSet.next()){
                //  把查询的数据  赋值给 userInfo1
                user.setId(resultSet.getInt("id"));// 获取用户id
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setType(resultSet.getInt("type"));
            }
            JdbcUtil.close(connection,preparedStatement,resultSet);//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public boolean updateuser(UserInfo userInfo) {
        // 创建连接
        try {
            Connection connection = JdbcUtil.getConnection();
            //  修改 sql 语句
            String sql = "update userinfo set ";
            PreparedStatement preparedStatement = null;
            // 如果username 不为空  sql语句加上对应的where条件
            if(userInfo.getUsername() != null && !userInfo.getUsername().equals("")){
                sql += "  username = ? ,type = ? where id = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,userInfo.getUsername());
                preparedStatement.setInt(2,userInfo.getType());
                preparedStatement.setInt(3,userInfo.getId());
            }else {
                // 用户密码 不为空
                sql += " password = ? where id = ? ";
                preparedStatement = connection.prepareStatement(sql);
                //preparedStatement.setString(1,"%"+userInfo.getUsername()+"%");
                preparedStatement.setString(1,userInfo.getPassword());
                preparedStatement.setInt(2,userInfo.getId());
            }
            System.out.println(preparedStatement);
            //  返回每一个sql的执行结果
            int ints = preparedStatement.executeUpdate();//  执行 sql 增删改语句
            JdbcUtil.close(connection,preparedStatement);
            if(ints > 0){//   sql执行成功
                return true;
            }else{
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public int getUserCount(String username) {
        // 创建连接
        int flag = 0;//  初始化
        try {
            Connection connection = JdbcUtil.getConnection();
            PreparedStatement preparedStatement = null;
            if(username != null && !username.equals("")){
                String sql = "select count(id) cou from userinfo where username like concat('%',?,'%')";//  cou  别名
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,username);
            }else{
                //  获取user表的记录总条数
                String sql = "select count(id) cou from userinfo ";//  cou  别名
                preparedStatement = connection.prepareStatement(sql);
            }
            System.out.println(preparedStatement);
            ResultSet resultSet = preparedStatement.executeQuery();//   查询
            resultSet.next();//  一定有返回值   如果没有数据  返回0
            flag = resultSet.getInt(1);//  resultSet.getInt("cou")
            JdbcUtil.close(connection,preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }
}
